﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Globalization;
using System.IO;
using System.Data.SqlClient;


namespace wfaFoxMigrator
{
    public sealed class DataFunctions : IDisposable
    {
        string connectionString;
        public string ConnectionString { get; set; }        

        #region Oracle Functions
        public string ConnectionStringOracle
        {
            get
            {
                if (String.IsNullOrEmpty(connectionString))
                {
                    List<string> connList = this.ConnectionString.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    string[] connlst = connList.Where(elm => !elm.ToLower().Contains("provider")).ToArray();
                    string cs = string.Join(";", connlst);
                    connectionString = cs;
                }
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }
        public DataTable ExecuteQueryOracle(ref string strSql)
        {
            DataTable ftDt = new DataTable();
            ftDt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (OracleConnection fpConn = new OracleConnection(this.ConnectionStringOracle))
                {
                    fpConn.Open();
                    using (OracleCommand fpComm = new OracleCommand(strSql, fpConn))
                    {
                        using (OracleDataAdapter fpDa = new OracleDataAdapter(fpComm))
                        {
                            fpDa.Fill(ftDt);
                        }
                    }
                    fpConn.Close();
                }
            }
            catch (OracleException ex)
            {
                strSql = "[Error]: " + ex.Message;
                ftDt = default(DataTable);
            }
            return ftDt;
        }
        public bool ExecuteNonQueryOracle(ref string strSql)
        {
            bool ans = true;
            using (OracleConnection fpConn = new OracleConnection(this.ConnectionStringOracle))
            {
                if (!String.IsNullOrEmpty(strSql))
                {
                    fpConn.Open();
                    try
                    {
                        using (OracleCommand fpComm = new OracleCommand(strSql, fpConn))
                        {
                            fpComm.ExecuteNonQuery();
                        }
                    }
                    catch (OracleException ex)
                    {
                        ans = false;
                        strSql = "[Error]: " + ex.Message;
                    }
                    fpConn.Close();
                }
                else
                {
                    ans = false;
                }
            }
            return ans;
        }
        public bool ExecuteNonQueryOracle(ref string strSql, List<OracleParameter> prms)
        {
            bool ans = true;

            using (OracleConnection fpConn = new OracleConnection(this.ConnectionStringOracle))
            {
                if (!String.IsNullOrEmpty(strSql))
                {
                    fpConn.Open();
                    try
                    {
                        using (OracleCommand fpComm = new OracleCommand(strSql.Replace("?", "'?'"), fpConn))
                        {
                            prms.ForEach(delegate(OracleParameter opm)
                            {
                                if (!fpComm.Parameters.Contains(opm))
                                {
                                    fpComm.Parameters.Add(opm);
                                }
                            });
                            fpComm.ExecuteNonQuery();
                            strSql = fpComm.ToString();
                        }
                    }
                    catch (OracleException ex)
                    {
                        ans = false;
                        strSql = "[Error]: " + ex.Message;
                    }
                    fpConn.Close();
                }
                else
                {
                    ans = false;
                }
            }
            return ans;
        }
        public bool ExecuteNonQueryOracle(ref string strSql, List<OracleParameter> prms, int RecordCount)
        {
            bool ans = true;

            using (OracleConnection fpConn = new OracleConnection(this.ConnectionStringOracle))
            {
                if (!String.IsNullOrEmpty(strSql))
                {
                    fpConn.Open();
                    try
                    {
                        using (OracleCommand fpComm = new OracleCommand(strSql.Replace("?", "'?'"), fpConn))
                        {
                            fpComm.ArrayBindCount = RecordCount;
                            fpComm.BindByName = true;
                            prms.ForEach(delegate(OracleParameter opm)
                            {
                                if (!fpComm.Parameters.Contains(opm))
                                {
                                    fpComm.Parameters.Add(opm);
                                }
                            });
                            fpComm.ExecuteNonQuery();
                            strSql = fpComm.ToString();
                        }
                    }
                    catch (OracleException ex)
                    {
                        ans = false;
                        strSql = "[Error]: " + ex.Message;
                    }
                    fpConn.Close();
                }
                else
                {
                    ans = false;
                }
            }
            return ans;
        }
        public bool BulkInsertOracle(ref string strSql, DataTable dt)
        {            
            bool ans = true;
            using (OracleConnection fpConn = new OracleConnection(this.ConnectionStringOracle))
            {
                if (!String.IsNullOrEmpty(strSql))
                {
                    fpConn.Open();
                    try
                    {
                        using (OracleCommand fpComm = new OracleCommand(strSql.Replace("?", "'?'"), fpConn))
                        {
                            fpComm.BindByName = true;
                            fpComm.ArrayBindCount = dt.Rows.Count;
                            foreach (DataColumn dc in dt.Columns)
                            {
                                dynamic arr = dt.Select(dc.ColumnName).Select(dr => dr[dc.ColumnName]).ToArray();
                                fpComm.Parameters.Add(dc.ColumnName, arr);
                            }
                            ans = fpComm.ExecuteNonQuery() > 0;
                            strSql = fpComm.ToString();
                        }
                    }
                    catch (OracleException ex)
                    {
                        ans = false;
                        strSql = "[Error]: " + ex.Message;
                    }
                    fpConn.Close();
                }
                else
                {
                    ans = false;
                }
            }
            return ans;

        }        
        #endregion

        #region Sql Functions
        public DataTable ExecuteQuerySql(ref string strSql)
        {
            DataTable ftDt = new DataTable();
            ftDt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (SqlConnection fpConn = new SqlConnection(this.ConnectionString))
                {
                    fpConn.Open();
                    using (SqlCommand fpComm = new SqlCommand(strSql, fpConn))
                    {
                        using (SqlDataAdapter fpDa = new SqlDataAdapter(fpComm))
                        {
                            fpDa.Fill(ftDt);
                        }
                    }
                    fpConn.Close();
                }
            }
            catch (Exception ex)
            {
                strSql = "[Error]: " + ex.Message;
                ftDt = default(DataTable);
            }
            return ftDt;
        }
        public bool ExecuteNonQuerySql(ref string strSql)
        {
            bool ans = true;
            using (SqlConnection fpConn = new SqlConnection(this.ConnectionString))
            {
                if (!String.IsNullOrEmpty(strSql))
                {
                    fpConn.Open();
                    try
                    {
                        using (SqlCommand fpComm = new SqlCommand(strSql, fpConn))
                        {
                            fpComm.ExecuteNonQuery();
                        }
                    }
                    catch (SqlException ex)
                    {
                        ans = false;
                        strSql = "[Error]: " + ex.Message;
                    }
                    fpConn.Close();
                }
                else
                {
                    ans = false;
                }
            }
            return ans;
        }
        public bool ExecuteNonQuerySql(ref string strSql, List<SqlParameter> prms)
        {
            bool ans = true;
            using (SqlConnection fpConn = new SqlConnection(this.ConnectionString))
            {
                if (!String.IsNullOrEmpty(strSql))
                {
                    fpConn.Open();
                    try
                    {
                        using (SqlCommand fpComm = new SqlCommand(strSql, fpConn))
                        {
                            prms.ForEach(delegate(SqlParameter opm)
                            {
                                fpComm.Parameters.Add(opm);
                            });
                            fpComm.ExecuteNonQuery();
                            strSql = fpComm.ToString();
                        }
                    }
                    catch (SqlException ex)
                    {
                        ans = false;
                        strSql = "[Error]: " + ex.Message;
                    }
                    fpConn.Close();
                }
                else
                {
                    ans = false;
                }
            }
            return ans;
        }
        #endregion

        #region Oledb And Odbc Functions
        public DataTable ExecuteQueryOleDb(ref string strSql)
        {
            DataTable ftDt = new DataTable();
            ftDt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (OleDbConnection fpConn = new OleDbConnection(this.ConnectionString))
                {
                    fpConn.Open();
                    using (OleDbCommand fpComm = new OleDbCommand(strSql, fpConn))
                    {
                        using (OleDbDataAdapter fpDa = new OleDbDataAdapter(fpComm))
                        {
                            fpDa.Fill(ftDt);
                        }
                    }
                    fpConn.Close();
                }
            }
            catch (OleDbException ex)
            {
                strSql = "[Error]: " + ex.Message;
                ftDt = default(DataTable);
            }
            return ftDt;
        }
        public DataTable ExecuteQueryODBC(ref string strSql)
        {
            DataTable ftDt = new DataTable();
            ftDt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (System.Data.Odbc.OdbcConnection fpConn = new System.Data.Odbc.OdbcConnection(this.ConnectionString))
                {
                    fpConn.Open();
                    using (System.Data.Odbc.OdbcCommand fpComm = new System.Data.Odbc.OdbcCommand(strSql, fpConn))
                    {
                        using (System.Data.Odbc.OdbcDataAdapter fpDa = new System.Data.Odbc.OdbcDataAdapter(fpComm))
                        {
                            fpDa.Fill(ftDt);
                        }
                    }
                    fpConn.Close();
                }
            }
            catch (Exception ex)
            {
                strSql = "[Error]: " + ex.Message;
                ftDt = default(DataTable);
            }
            return ftDt;
        }           
        #endregion
        
        public DataTable GetSchemaFromQuery(string strSql)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (System.Data.OleDb.OleDbConnection fpConn = new OleDbConnection(this.ConnectionString))
                {
                    fpConn.Open();
                    using (OleDbCommand fpComm = new OleDbCommand(strSql, fpConn))
                    {
                        using (OleDbDataAdapter fpDa = new OleDbDataAdapter(fpComm))
                        {
                            fpDa.FillSchema(dt, SchemaType.Source);
                        }
                    }
                    fpConn.Close();
                }
            }
            catch (OleDbException ex)
            {
                dt = default(DataTable);
                strSql = "[Error]: " + ex.Message;
            }
            return dt;
        }
        //TODO: Finish the datatable stuff to have only one query to db
        public DataTable PrepareToMigrateThis(ListItem lst, ref SortedList<string, DataTable> lstSchemas,ref SortedList<string, DataTable> lstData, out string result)
        {
            result = string.Empty;
            DataTable dt = new DataTable(lst.Text);
            DataTable dtS = new DataTable(lst.Text);
            string strSqlAlt = string.Empty;
            //string constr = String.Format(Properties.Settings.Default.FoxProCS, Properties.Settings.Default.InitialPath);
            if (File.Exists(Properties.Settings.Default.MapFilesPath + @"\" +lst.Text + ".txt"))
            {
                strSqlAlt = File.ReadAllText(Properties.Settings.Default.MapFilesPath + @"\" + lst.Text + ".txt").Replace("\n",string.Empty).Replace("\r",string.Empty);
            }
            string strSql = String.Format(Properties.Settings.Default.SelectClause, " * ", lst.Text);
            if (lstSchemas.Count > 0)
            {
                if (!lstSchemas.Any(sch => sch.Key.Equals(lst.Text)))
                {
                    result = getDataAndSchema(lst, strSql, ref dt, ref dtS, ref lstSchemas, ref lstData);
                }
                else
                {
                    if (lstData.ContainsKey(lst.Text))
                    {
                        dt = lstData[lst.Text];
                    }                    
                }
            }
            else
            {
                result = getDataAndSchema(lst, strSql, ref dt, ref dtS, ref lstSchemas, ref lstData);
            }            
            if (dtS == null || dt == null)
            {   
                if (!String.IsNullOrEmpty(strSqlAlt))
                {
                    result = getDataAndSchema(lst, strSqlAlt, ref dt, ref dtS, ref lstSchemas, ref lstData);
                }
                if (dtS == null || dt == null)
                {
                    result = String.Format(Properties.Settings.Default.UnexpectedError, lst.Text, strSql) + Environment.NewLine;
                }
            }
            return dt;
        }
        private string getDataAndSchema(ListItem lst, string strSql, ref DataTable dt, ref DataTable dtS, ref SortedList<string, DataTable> lstSchemas, ref SortedList<string, DataTable> lstData)
        {
            string result = string.Empty;
            string constr = String.Format(Properties.Settings.Default.FoxProCS, lst.FullPath);
            try
            {
                using (DataFunctions dfcs = new DataFunctions { ConnectionString = constr })
                {
                    string strSqlOriginal = strSql;
                    dt = dfcs.ExecuteQueryOleDb(ref strSql);
                    if (dt == null && CommonFunctions.CheckIfItsJetEnabled())
                    {
                        dfcs.ConnectionString = Properties.Settings.Default.JetCS.Replace("{0}", lst.FullPath.ToLower().Replace(lst.TableName.ToLower(), string.Empty));
                        strSqlOriginal += ".dbf";
                        dt = dfcs.ExecuteQueryODBC(ref strSqlOriginal);
                    }
                    if (dt != null)
                    {
                        dtS = dt.Clone();
                        dtS.Clear();
                    }
                }
                if (dtS != null)
                {
                    if (lstSchemas.ContainsKey(lst.Text))
                    {
                        lstSchemas.Remove(lst.Text);
                    }
                    if (lstData.ContainsKey(lst.Text))
                    {
                        lstData.Remove(lst.Text);
                    }
                    lstSchemas.Add(lst.Text, dtS);
                    if (dt != null)
                    { 
                        if (dt.Rows.Count > 0)
                        {
                            lstData.Add(lst.Text, dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                dt = null;
                dtS = null;
                result = ex.Message;
            }
            return result;
        }
        private DataTable getDataOnly(ListItem lst, string strSql, ref SortedList<string, DataTable> lstData)
        {
            DataTable dt = new DataTable();
            string result = string.Empty;
            string constr = String.Format(Properties.Settings.Default.FoxProCS, lst.FullPath);
            try
            {
                using (DataFunctions dfcs = new DataFunctions { ConnectionString = constr })
                {
                    string strSqlOriginal = strSql;
                    dt = dfcs.ExecuteQueryOleDb(ref strSql);
                    if (dt == null && CommonFunctions.CheckIfItsJetEnabled())
                    {
                        dfcs.ConnectionString = Properties.Settings.Default.JetCS.Replace("{0}", lst.FullPath.ToLower().Replace(lst.TableName.ToLower(), string.Empty));
                        strSqlOriginal += ".dbf";
                        dt = dfcs.ExecuteQueryODBC(ref strSqlOriginal);
                    }                    
                }
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        if (lstData.ContainsKey(lst.Text))
                        {
                            lstData.Remove(lst.Text);
                        }                        
                        if (dt != null)
                        {
                            if (dt.Rows.Count > 0)
                            {
                                lstData.Add(lst.Text, dt);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                dt = null;               
                result = ex.Message;
            }
            return dt;
        }
        #region IDisposable Members

        public void Dispose()
        {
            GC.SuppressFinalize(this);
            GC.Collect();
        }

        #endregion
    }
}
